package main.synch;

import com.primavera.integration.client.bo.BusinessObjectException;
import com.primavera.integration.client.bo.object.*;
import main.Primavera.LoginFunctions;
import main.SQL.MsSQLConnect;
import main.SQL.SQLTaskRsrc;
import main.Trees.ProjectNode;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;

/* Класс работы с бизнес-объектами в SQL "Factage" */
public class SQLProject {

    private Connection connSql;

    //region Объявление поля для синхронизации
    private PrimaveraProject project;
    private SectorCode[] codes;
    private HashMap<Integer, SectorCode> sectorCodeTable;
    //endregion

    //region Объявление полей для валидации
    private ArrayList<SQLTaskRsrc> taskRsrcList;
    private HashMap<Integer, Date> taskMap;
    private ProjectNode curProject;
    private HashSet<String> parentSet;
    private HashMap <String, String> epsMap, smetMap, virtualSmetMap;
    private HashMap<String, ArrayList<Integer>> taskSmetMap;
    private String dbName;
    //endregion

    public SQLProject() {}

    //region Методы для процесса синхронизации

    //region synchSQLProject(PrimaveraProject project) - запуск процесса загрузки и обновления бизнес-объектов в SQL
    public void synchSQLProject(PrimaveraProject project) {
        connSql = MsSQLConnect.ConnectDB("Factage"); //ssqlphn1d
        this.project = project;

        try {

            prepareSynchTables();

            synchProjectTable();
            synchEpsTable();
            synchWbsMap();
            synchTaskTable();
            synchRsrcMap();

            synchActvCodeTypeMap();
//            synchDocMap();

            clearSynchTables();

            connSql.close();

        } catch (SQLException | BusinessObjectException e) {
            e.printStackTrace();
        }
    }
    //endregion

    //region prepareSynchTables() - подготовка таблиц к синхронизации - обнуление флагов, вызов из synchSQLProject(PrimaveraProject project)
    private void prepareSynchTables() throws SQLException, BusinessObjectException {

        CallableStatement stmt = connSql.prepareCall( "{call prepareTables( ?, ? )}" );

        stmt.setString( 1, project.getDbName() );
        stmt.setInt( 2, project.getCurProject().getObjectId().toInteger() );

        stmt.execute();

        stmt.close();

    }
    //endregion

    //region synchProjectTable() - добавление или обновление проекта в SQL, вызов из synchSQLProject(PrimaveraProject project)
    private void synchProjectTable() throws SQLException, BusinessObjectException {
        CallableStatement stmt = connSql.prepareCall("{call synchProject(?, ?, ?, ?, ?, ?, ?, ?)}");

        stmt.setString(1, project.getDbName());
        stmt.setInt(2, project.getCurProject().getObjectId().toInteger());
        stmt.setString(3, project.getCurProject().getId());
        stmt.setString(4, project.getCurProject().getName());
        stmt.setInt(5, project.getCurProject().getWBSObjectId().toInteger());
        stmt.setString(6, project.getProjCode() != null ? project.getProjCode().getProjectCodeValue() : null);
        stmt.setInt(7, project.getCurProject().getParentEPSObjectId().toInteger());
        stmt.registerOutParameter(8, Types.INTEGER);

        stmt.execute();
        stmt.close();

    }
    //endregion

    //region synchEpsTable() - загрузка стека WBS в SQL, вызов из synchSQLProject(PrimaveraProject project)
    private void synchEpsTable() throws SQLException, BusinessObjectException {
        HashMap<Integer, EPS> epsTable = project.getEPSTable();

        Integer parent = project.getCurProject().getParentEPSObjectId().toInteger();
        CallableStatement stmt = connSql.prepareCall("{call synchEPS(?, ?, ?, ?, ?)}");
        while ( parent != 0 ) {
            EPS curEps = epsTable.get(parent);

            stmt.setString( 1, project.getDbName() );
            stmt.setInt(2, curEps.getObjectId().toInteger());
            stmt.setString(3, curEps.getId());
            stmt.setString(4, curEps.getName());
            stmt.setInt( 5, curEps.getParentObjectId() == null ? 0 : curEps.getParentObjectId().toInteger() );

            stmt.addBatch();
            parent = curEps.getParentObjectId() == null ? 0 : curEps.getParentObjectId().toInteger();
        }
        stmt.executeBatch();

        stmt.close();

    }
    //endregion

    //region synchWbsMap() - загрузка стека WBS в SQL, вызов из synchSQLProject(PrimaveraProject project)
    private void synchWbsMap() throws SQLException, BusinessObjectException {
        CallableStatement stmt = connSql.prepareCall( "{call insertWbsBatch(?, ?, ?, ?, ?, ?, ?)}" );

        for (WBS curWbs : project.getWbsMap().values()) {
            stmt.setString(1, project.getDbName());
            stmt.setInt(2, curWbs.getObjectId().toInteger());
            stmt.setString(3, curWbs.getCode());
            stmt.setString(4, curWbs.getName());
            stmt.setInt(5, curWbs.getParentObjectId().toInteger());
            stmt.setInt(6, project.getCurProject().getObjectId().toInteger());
            stmt.setInt(7, curWbs.getSequenceNumber());

            stmt.addBatch();
       }

        stmt.executeBatch();
        stmt.close();

    }
    //endregion

    //region synchWbsMap() - загрузка стека WBS в SQL, вызов из synchSQLProject(PrimaveraProject project)
    private void synchActvCodeTypeMap() throws SQLException, BusinessObjectException {
        CallableStatement stmt = connSql.prepareCall( "{call insertActvCodeType(?, ?, ?, ?, ?)}" );

        for (ActivityCodeType curCodeType : project.getMapActvCodeType().values()) {
            stmt.setString(1, project.getDbName());
            stmt.setInt(2, project.getCurProject().getObjectId().toInteger());
            stmt.setInt(3, curCodeType.getObjectId().toInteger());
            stmt.setString(4, curCodeType.getName());
            stmt.setString(5, curCodeType.getScope().toString());

            stmt.addBatch();
        }

        stmt.executeBatch();
        stmt.close();

        stmt = connSql.prepareCall( "{call insertActvCode(?, ?, ?, ?, ?, ?)}" );

        for (ActivityCode curCode : project.getMapActvCode().values()) {
            stmt.setString(1, project.getDbName());
            stmt.setInt(2, curCode.getObjectId().toInteger());
            stmt.setInt(3, curCode.getCodeTypeObjectId().toInteger());
            stmt.setInt(4, curCode.getParentObjectId() != null ? curCode.getParentObjectId().toInteger() : 0);
            stmt.setString(5, curCode.getCodeValue());
            stmt.setString(6, curCode.getDescription());

            stmt.addBatch();
        }

        stmt.executeBatch();
        stmt.close();

        stmt = connSql.prepareCall( "{call insertActvCodeAssign(?, ?, ?, ?, ?)}" );

        System.out.println("project.getMapActvCodeAssign().values() = " + project.getMapActvCodeAssign().values().size());

        for (ActivityCodeAssignment curCodeAssign : project.getMapActvCodeAssign().values()) {
            stmt.setString(1, project.getDbName());
            stmt.setInt(2, curCodeAssign.getActivityCodeObjectId().toInteger());
            stmt.setInt(3, curCodeAssign.getActivityCodeTypeObjectId().toInteger());
            stmt.setInt(4, curCodeAssign.getActivityObjectId().toInteger());
            stmt.setInt(5, project.getCurProject().getObjectId().toInteger());

            stmt.addBatch();
        }

        stmt.executeBatch();
        stmt.close();

    }
    //endregion

    //region synchTaskTable() - подготовка и загрузка стека работ в SQL, вызов из synchSQLProject(PrimaveraProject project)
    private void synchTaskTable() throws SQLException, BusinessObjectException {

        CallableStatement stmt;

        //region Загрузка таблиц из PrimaveraProject
        HashMap<Integer, ActivityCodeAssignment> actCodeAssTable = project.getActCodeAssTable();
        HashMap<Integer, ActivityCodeAssignment> actStageCodeAssTable = project.getActStageCodeAssTable();
        HashMap<Integer, ActivityCodeAssignment> actCodeAssNPZTable = project.getActCodeAssNPZTable();
        HashMap<Integer, ActivityCodeAssignment> WDCodeAssTable = project.getWDCodeAssTable();
        HashMap<Integer, ActivityCode> WDCodeTable = project.getWDCodeTable();
        HashMap<Integer, ActivityCodeAssignment> SectorCodeAssTable = project.getSectorCodeAssTable();
        sectorCodeTable = project.getSectorCodeTable();
        HashMap<Integer, UDFValue> UDFPVTable = project.getUDFPVTable();
        HashMap<Integer, UDFValue> UDFUOMTable = project.getUDFUOMTable();
        //endregion

//        System.out.println ("tasksQuantity = " + taskCol.size());

        stmt = connSql.prepareCall("{call insertTaskBatchNew(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

        for (Activity curTask : project.getTaskTable().values()) {

            //region Назначение кода "РН: ВИД РАБОТ"
            String actCode = "";
            if ( actCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ) actCode = actCodeAssTable.get( curTask.getObjectId().toInteger() ).getActivityCodeValue();
            //endregion

            //region Назначение кода "РН: СТАДИЯ*"
            String actStageCode = "";
            if ( actStageCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ) actStageCode = actStageCodeAssTable.get( curTask.getObjectId().toInteger() ).getActivityCodeValue();
            //endregion

            //region Назначение кода "РН: ВИД РАБОТ *НПЗ"
            String actCodeNPZ = "";
            String actCodeNPZdescr = "";
            if ( actCodeAssNPZTable.containsKey( curTask.getObjectId().toInteger())) {
                actCodeNPZ = actCodeAssNPZTable.get(curTask.getObjectId().toInteger()).getActivityCodeValue();
                actCodeNPZdescr = actCodeAssNPZTable.get(curTask.getObjectId().toInteger()).getActivityCodeDescription();
            }
            //endregion

            //region Назначение кода работы "РН: РД*", а также добавление идентификатора родительского элемента
            String WDCodeValue = "";
            if ( WDCodeAssTable.containsKey(curTask.getObjectId().toInteger()) ) {
                ActivityCode WDCode = WDCodeTable.get(WDCodeAssTable.get(curTask.getObjectId().toInteger()).getActivityCodeObjectId().toInteger());
                WDCodeValue = WDCode.getCodeValue();
                if (WDCode.getParentObjectId() != null && WDCodeTable.containsKey(WDCode.getParentObjectId().toInteger())) {
                    WDCodeValue = WDCodeTable.get(WDCode.getParentObjectId().toInteger()).getCodeValue() + "" + WDCode.getCodeValue();
                }
            }
            //endregion

            //region Назначение кодов работ "Физ.объем" и "Ед изм"
            String UDFPV = "";
            if ( UDFPVTable.containsKey( curTask.getObjectId().toInteger() ) ) UDFPV = UDFPVTable.get( curTask.getObjectId().toInteger() ).getText();

            String UDFUOM = "";
            if ( UDFUOMTable.containsKey( curTask.getObjectId().toInteger() ) ) UDFUOM = UDFUOMTable.get( curTask.getObjectId().toInteger() ).getText();
            //endregion

            //region Назначение кодов "РН: СЕКТОРА *НПЗ"
            this.codes = new SectorCode [] { null,null,null,null };
            if ( SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ) {
                SectorCode sectorCode = sectorCodeTable.get(SectorCodeAssTable.get(curTask.getObjectId().toInteger()).getActivityCodeObjectId().toInteger());
//                    System.out.println("sectorCode = " + sectorCode);
//                    System.out.println("sectorCode.getDescr()" + sectorCode.getDescr());
                getSectorCodeHierarchy(sectorCode);
            }
            //endregion

            stmt.setString(1, project.getDbName());
            stmt.setInt(2, curTask.getObjectId().toInteger());
            stmt.setString(3, curTask.getId());
            stmt.setString(4, curTask.getName());
            stmt.setInt(5, curTask.getWBSObjectId().toInteger());
            stmt.setFloat(6, curTask.getPercentComplete().floatValue());
            stmt.setString(7, String.valueOf(curTask.getType()));
            stmt.setInt(8, project.getCurProject().getObjectId().toInteger());
            stmt.setString(9, actCode );
            stmt.setString(10, UDFPV);
            stmt.setString(11, UDFUOM );
            stmt.setDate(12, curTask.getActualStartDate());
            stmt.setDate(13, curTask.getActualFinishDate());

            stmt.setString(14, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[0]!=null ? codes[0].getValue(): null ): null);
            stmt.setString(15, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[0]!=null ? codes[0].getDescr(): null ): null);
            stmt.setString(16, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[1]!=null ? codes[1].getValue(): null ): null);
            stmt.setString(17, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[1]!=null ? codes[1].getDescr(): null ): null);
            stmt.setString(18, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[2]!=null ? codes[2].getValue(): null ): null);
            stmt.setString(19, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[2]!=null ? codes[2].getDescr(): null ): null);
            stmt.setString(20, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[3]!=null ? codes[3].getValue(): null ): null);
            stmt.setString(21, SectorCodeAssTable.containsKey( curTask.getObjectId().toInteger() ) ? (codes[3]!=null ? codes[3].getDescr(): null ): null);

            stmt.setFloat(22, (float) 0.00);
            stmt.setString(23, WDCodeValue );
            stmt.setString(24, actCodeNPZ );
            stmt.setString(25, actCodeNPZdescr );
            stmt.setString(26, actStageCode );


            stmt.addBatch();
        }

        stmt.executeBatch();

        stmt.close();

    }
    //endregion

    private SectorCode[] getSectorCodeHierarchy(SectorCode code) {
        if (code.getLevel() > 0) {
            getSectorCodeHierarchy(sectorCodeTable.get(code.getParentId()));
        }
        codes[code.getLevel()] = code;
        return codes;
    }

    //region synchRsrcMap() - загрузка стека ресурсов в SQL, вызов из synchSQLProject(PrimaveraProject project)
    private void synchRsrcMap() throws SQLException, BusinessObjectException {

        CallableStatement stmt = connSql.prepareCall("{call insertRsrcBatch(?, ?, ?, ?, ?, ?)}");

        for (Resource curRsrc : project.getRsrcMap().values()) {

            stmt.setString(1, project.getDbName());
            stmt.setInt(2, curRsrc.getObjectId().toInteger());
            stmt.setString(3, curRsrc.getId());
            stmt.setString(4, curRsrc.getResourceType().toString());
            stmt.setString(5, curRsrc.getName());
            stmt.setInt(6, curRsrc.getParentObjectId().toInteger());

            stmt.addBatch();
        }

        stmt.executeBatch();

        stmt.close();

    }
    //endregion

    //region clearSynchTables() - очистка бизнес-объектов не обновленных при синхронизации, вызов из synchSQLProject(PrimaveraProject project)
    private void clearSynchTables() {
        try {

            CallableStatement stmt = connSql.prepareCall( "{call clearSynchTables( ?, ? )}" );

            stmt.setString( 1, project.getDbName() );
            stmt.setInt( 2, project.getCurProject().getObjectId().toInteger() );

            stmt.execute();

            stmt.close();

        } catch ( SQLException | BusinessObjectException e ) { e.printStackTrace(); }

    }
    //endregion

    //endregion


    //region Методы для процесса валидации

    //region validSQLProject(ProjectNode curProject, LoginFunctions loginFnc) - запуск выгрузки проекта из SQL для валидации, вызов из класса Synch
    public void validSQLProject(ProjectNode curProject, LoginFunctions loginFnc) {

        System.out.println("validSQLProject");

        connSql = MsSQLConnect.ConnectDB("Factage"); //ssqlphn1d
        this.curProject = curProject;

        dbName = loginFnc.session.getDatabaseName();

        try {

            validTaskRsrcList();
            tasksForValid();
            validAssignedSmet();
            validTaskSmet();

            connSql.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //endregion

    //region validTaskRsrcList() - выгрузка из SQL назначений ресурсов в ArrayList taskRsrcList, вызов из validSQLProject
    private void validTaskRsrcList() throws SQLException {

        System.out.println("validtaskRsrcList");
        this.taskRsrcList = new ArrayList<> ();

        CallableStatement stmt = connSql.prepareCall( "{call getTaskRsrcForValid(?, ?)}" );

        stmt.setString( 1, dbName );
        stmt.setInt( 2, curProject.getId() );

        ResultSet rs = stmt.executeQuery();

        while ( rs.next() ) {
            this.taskRsrcList.add(new SQLTaskRsrc(rs.getInt("task_rsrc_id"), rs.getInt("task_id"), rs.getInt("rsrc_id"), rs.getDouble("task_rsrc_planned_units"), rs.getString("date_update"), rs.getString("db_name"), rs.getInt("proj_id"), rs.getString("rsrc_short_name")));
        }

        rs.close();
        stmt.close();
    }
    //endregion

    //region tasksForValid() - выгрузка из SQL идентификаторов работ, для которых есть связи, в HasMap taskMap, вызов из validSQLProject
    private void tasksForValid() throws SQLException {

        System.out.println("tasksForValid");
        this.taskMap = new HashMap<> ();

        CallableStatement stmt = connSql.prepareCall( "{call getTasksForValid(?, ?)}" );

        stmt.setString( 1, dbName );
        stmt.setInt( 2, curProject.getId() );
        ResultSet rs = stmt.executeQuery();

        System.out.println("tasksForValid");

        while ( rs.next() ) {
            this.taskMap.put(rs.getInt("task_id"), rs.getDate("actual_finish_date"));
        }

        rs.close();
        stmt.close();
    }
    //endregion

    private void validAssignedSmet() throws SQLException {

        this.parentSet = new HashSet<>();
        this.epsMap = new HashMap<>();
        this.smetMap = new HashMap<>();
        this.virtualSmetMap = new HashMap<>();

        CallableStatement stmt = connSql.prepareCall( "{call getAssignedSmetDocArchive(?, ?)}" );
        stmt.setString( 1, dbName );
        stmt.setInt( 2, curProject.getId() );
        ResultSet rs = stmt.executeQuery();

        while ( rs.next() ) {
            this.parentSet.add(rs.getString("parent"));
            this.epsMap.put(rs.getString("eps"), rs.getString("parent"));
            if (rs.getInt("virtual") == 1) this.virtualSmetMap.put(rs.getString("smet"), rs.getString("eps"));
            else this.smetMap.put(rs.getString("smet"), rs.getString("eps"));
        }

        stmt = connSql.prepareCall( "{call getAssignedSmetImportFactage(?, ?)}" );
        stmt.setString( 1, dbName );
        stmt.setInt( 2, curProject.getId() );
        rs = stmt.executeQuery();

        while ( rs.next() ) {
            this.parentSet.add(rs.getString("parent"));
            this.epsMap.put(rs.getString("eps"), rs.getString("parent"));
            if (rs.getInt("virtual") == 1) this.virtualSmetMap.put(rs.getString("smet"), rs.getString("eps"));
            else this.smetMap.put(rs.getString("smet"), rs.getString("eps"));
        }

        rs.close();
        stmt.close();

    }

    private void validTaskSmet() throws SQLException {

        this.taskSmetMap = new HashMap<>();

        CallableStatement stmt = connSql.prepareCall( "{call getTaskSmetDocArchive(?, ?)}" );
        stmt.setString( 1, dbName );
        stmt.setInt( 2, curProject.getId() );
        ResultSet rs = stmt.executeQuery();

        int i = 0;
        while ( rs.next() ) {
                i++;
                if (this.taskSmetMap.containsKey(rs.getString("smet"))) this.taskSmetMap.get(rs.getString("smet")).add(rs.getInt("task_id"));
                else {
                    this.taskSmetMap.put(rs.getString("smet"), new ArrayList<Integer>());
                    this.taskSmetMap.get(rs.getString("smet")).add(rs.getInt("task_id"));
                }
        }
        System.out.println("SQL records = " + i);

        stmt = connSql.prepareCall( "{call getTaskSmetImportFactage(?, ?)}" );
        stmt.setString( 1, dbName );
        stmt.setInt( 2, curProject.getId() );
        rs = stmt.executeQuery();

        while ( rs.next() ) {
            if (this.taskSmetMap.containsKey(rs.getString("smet"))) this.taskSmetMap.get(rs.getString("smet")).add(rs.getInt("task_id"));
            else {
                this.taskSmetMap.put(rs.getString("smet"), new ArrayList<Integer>());
                this.taskSmetMap.get(rs.getString("smet")).add(rs.getInt("task_id"));
            }        }

        rs.close();
        stmt.close();

    }

    //region getTaskRsrcList(), getTaskMap(), getParentSet(), getEpsMap(), getSmetMap() - Геттеры для выгружаемых данных
    public ArrayList<SQLTaskRsrc> getTaskRsrcList() {
        return taskRsrcList;
    }

    public HashMap<Integer, Date> getTaskMap() {
        return taskMap;
    }

    public HashSet<String> getParentSet() {
        return parentSet;
    }

    public HashMap<String, String> getEpsMap() {
        return epsMap;
    }

    public HashMap<String, String> getSmetMap() {
        return smetMap;
    }

    public HashMap<String, String> getVirtualSmetMap() {
        return virtualSmetMap;
    }

    public HashMap<String, ArrayList<Integer>> getTaskSmetMap() {
        return taskSmetMap;
    }

    //endregion

    //endregion
}
